package com.example.prison.mapper;

import com.example.prison.entity.VisitorAppointment;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
* @author jiang
* @description 针对表【visitor_appointment】的数据库操作Mapper
* @createDate 2024-06-03 09:12:46
* @Entity com.example.prison.entity.VisitorAppointment
*/
@Mapper
public interface VisitorAppointmentMapper extends BaseMapper<VisitorAppointment> {

    @Select("<script>select * from visitor_appointment " +
            " <where> " +
            " <if test=\"visitorName != null and visitorName != ''\">and visitor_name like concat('%',#{visitorName},'%') </if> " +
            " <if test=\"beginTime != null and beginTime != ''\">and appointment_time &gt;= #{beginTime} </if> " +
            " <if test=\"endTime != null and endTime != ''\">and appointment_time &lt;= #{endTime} </if> " +
            " <if test=\"appointmentStatus != null and appointmentStatus != ''\">and appointment_status like concat('%',#{appointmentStatus},'%') </if> " +
            " <if test=\"visitorStatus != null and visitorStatus != ''\">and visitor_status like concat('%',#{visitorStatus},'%') </if> " +
            " <if test=\"visitDepartment != null and visitDepartment != ''\">and visit_department like concat('%',#{visitDepartment},'%') </if> " +
            " </where> " +
            "order by id desc</script>")
    List<VisitorAppointment> get(@Param("visitorName")String visitorName,@Param("beginTime")String beginTime,@Param("endTime")String endTime,
                                 @Param("appointmentStatus")String appointmentStatus,@Param("visitorStatus")String visitorStatus,@Param("visitDepartment")String visitDepartment);

    @Insert("insert into visitor_appointment(visitor_name, id_card, phone, visit_reason, is_car, car_number, car_license, visit_department, receptionist, appointment_time, appointment_status, visitor_status) " +
            "values (#{visitorName}, #{idCard}, #{phone}, #{visitReason}, #{isCar}, #{carNumber}, #{carLicense}, #{visitDepartment}, #{receptionist}, #{appointmentTime}, #{appointmentStatus}, #{visitorStatus})")
    int add(VisitorAppointment visitor);

    @Delete("<script>delete from visitor_appointment where id in <foreach collection=\"ids\" item=\"item\" open=\"(\" separator=\",\" close=\")\">#{item} </foreach> </script>")
    void deleteVisitorAppointment(@Param("ids") Integer[] ids);

//    @Update("update visitor_appointment set visitor_name=#{visitor.visitorName},id_card=#{visitor.idCard},phone=#{visitor.phone}," +
//            "visit_reason=#{visitor.visitReason},is_car=#{visitor.isCar},car_number=#{visitor.carNumber},car_license=#{visitor.carLicense}," +
//            "visit_department=#{visitor.visitDepartment},receptionist=#{visitor.receptionist},appointment_time=#{visitor.appointmentTime}," +
//            "appointment_status=#{visitor.appointmentStatus},visitor_status=#{visitor.visitorStatus} " +
//            "where id = #{id}")
    @Update({"<script> UPDATE visitor_appointment",
            "<set>",
            "<if test='visitor.visitorName != null'>visitor_name = #{visitor.visitorName},</if>",
            "<if test='visitor.idCard != null'>id_card = #{visitor.idCard},</if>",
            "<if test='visitor.phone != null'>phone = #{visitor.phone},</if>",
            "<if test='visitor.visitReason != null'>visit_reason = #{visitor.visitReason},</if>",
            "<if test='visitor.isCar != null'>is_car = #{visitor.isCar},</if>",
            "<if test='visitor.carNumber != null'>car_number = #{visitor.carNumber},</if>",
            "<if test='visitor.carLicense != null'>car_license = #{visitor.carLicense},</if>",
            "<if test='visitor.visitDepartment != null'>visit_department = #{visitor.visitDepartment},</if>",
            "<if test='visitor.receptionist != null'>receptionist = #{visitor.receptionist},</if>",
            "<if test='visitor.appointmentTime != null'>appointment_time = #{visitor.appointmentTime},</if>",
            "<if test='visitor.appointmentStatus != null'>appointment_status = #{visitor.appointmentStatus},</if>",
            "<if test='visitor.visitorStatus != null'>visitor_status = #{visitor.visitorStatus},</if>",
            "<if test='visitor.inGateSentry != null'>in_gate_sentry = #{visitor.inGateSentry},</if>",
            "<if test='visitor.inWarehouse != null'>in_warehouse = #{visitor.inWarehouse},</if>",
            "<if test='visitor.inDutyPolice != null'>in_duty_police = #{visitor.inDutyPolice},</if>",
            "<if test='visitor.outGateSentry != null'>out_gate_sentry = #{visitor.outGateSentry},</if>",
            "<if test='visitor.outWarehouse != null'>out_warehouse = #{visitor.outWarehouse},</if>",
            "<if test='visitor.outDutyPolice != null'>out_duty_police = #{visitor.outDutyPolice},</if>",
            "<if test='visitor.outReceptionist != null'>out_receptionist = #{visitor.outReceptionist},</if>",
            "</set>",
            "where id = #{id} </script>"})
    int updateVisitorAppointment(VisitorAppointment visitor, Integer id);

    @Select("select * from visitor_appointment where id=#{id}")
    VisitorAppointment getById(@Param("id") Integer id);

    @Select("select * from visitor_appointment where id_card=#{idCard} order by appointment_time desc LIMIT 1")
    VisitorAppointment getByIdCard(@Param("idCard") String idCard);

    @Select("select * from visitor_appointment where visitor_name like concat('%',#{visitorName},'%') order by id desc")
    List<VisitorAppointment> getByName(@Param("visitorName") String visitorName);

    @Select("select * from visitor_appointment where visitor_name=#{visitorName}")
    VisitorAppointment getOneByName(@Param("visitorName") String visitorName);

    @Select("select SUM(car_number) from visitor_appointment where appointment_status =#{appointmentStatus}")
    String getCarNumberSum(@Param("appointmentStatus") String appointmentStatus);

    @Select("<script>select * from visitor_appointment " +
            " <where> appointment_time =#{time} and appointment_status=1 " +
            " <if test=\"visitorName != null and visitorName != ''\">and visitor_name like concat('%',#{visitorName},'%') </if> " +
            " </where> " +
            "order by id desc</script>")
    List<VisitorAppointment> getTodayVisitor(@Param("time") String time, @Param("visitorName")String visitorName);

    @Select("select SUM(car_number) from visitor_appointment where appointment_time =#{appointmentTime} and appointment_status=1 and car_number!=0 order by id desc")
    String getTodayVisitorCarSum(@Param("appointmentTime") String appointmentTime);

    @Select("<script>select * from visitor_appointment " +
            " <where> appointment_time =#{appointmentTime} and appointment_status=1 and car_number!=0 " +
            " <if test=\"carLicense != null and carLicense != ''\">and car_license like concat('%',#{carLicense},'%') </if> " +
            " </where> " +
            "order by id desc</script>")
    List<VisitorAppointment> getTodayVisitorCar(@Param("appointmentTime") String appointmentTime, @Param("carLicense")String carLicense);

    @Select("<script>select * from visitor_appointment " +
            " <where> visitor_status=#{visitorStatus} and appointment_time &lt;= #{appointmentTime} " +
            " <if test=\"visitorName != null and visitorName != ''\">and visitor_name like concat('%',#{visitorName},'%') </if> " +
            " <if test=\"beginTime != null and beginTime != ''\">and appointment_time &gt;= #{beginTime} </if> " +
            " <if test=\"endTime != null and endTime != ''\">and appointment_time &lt;= #{endTime} </if> " +
            " </where> " +
            "order by id desc</script>")
    List<VisitorAppointment> getByStatusAndTime(@Param("visitorStatus")String visitorStatus, @Param("appointmentTime")String appointmentTime,
                                                @Param("visitorName")String visitorName,@Param("beginTime")String beginTime,@Param("endTime")String endTime);

    @Select("<script>select * from visitor_appointment " +
            " <where> visitor_status!='5' and visitor_status!='0' and appointment_time &lt;= #{appointmentTime} " +
            " <if test=\"visitorName != null and visitorName != ''\">and visitor_name like concat('%',#{visitorName},'%') </if> " +
            " <if test=\"beginTime != null and beginTime != ''\">and appointment_time &gt;= #{beginTime} </if> " +
            " <if test=\"endTime != null and endTime != ''\">and appointment_time &lt;= #{endTime} </if> " +
            " </where> " +
            "order by id desc</script>")
    List<VisitorAppointment> getRetentionVisitor(@Param("visitorStatus")String visitorStatus, @Param("appointmentTime")String appointmentTime,
                                                @Param("visitorName")String visitorName,@Param("beginTime")String beginTime,@Param("endTime")String endTime);

    @Select("<script>select * from visitor_appointment " +
            " <where> visitor_status!='5' " +
            " <if test=\"visitorName != null and visitorName != ''\">and visitor_name like concat('%',#{visitorName},'%') </if> " +
            " <if test=\"appointmentTime != null and appointmentTime != ''\">and appointment_time &lt;= #{appointmentTime} </if> " +
            " </where> " +
            "order by appointmentTime desc</script>")
    List<VisitorAppointment> getByNameAndTimeOrderTime(@Param("visitorName")String visitorName, @Param("appointmentTime")String appointmentTime);

    @Select("<script>select * from visitor_appointment where phone=#{pid} and appointment_time=#{appointmentTime} LIMIT 1</script>")
    VisitorAppointment getByPIDAndTime(@Param("pid")String pid, @Param("appointmentTime")String appointmentTime);
}




